/***
This do-file creates a figure comparing the changes in average daily spending
for each month in the Affinity Solutions credit and debit card data to the 
Advance Monthly Retail Trade Survey (MARTS).
***/

*-------------------------------------------------------------------------------
* Set up
*-------------------------------------------------------------------------------

* Set $root 
project figstabs, root
if (r(buildrunning)==0) include "${root}/code/config_interactive.do"

* Set globals
project, uses("${root}/code/set_globals.do")
include "${root}/code/set_globals.do"
local category "Spending"

* Create required subfolders
cap mkdir "${root}/results/Spending"
cap mkdir "${root}/results/paper numbers"
cap mkdir "${root}/results/paper numbers/`category'"

*-------------------------------------------------------------------------------
* Load MARTS data
*-------------------------------------------------------------------------------

* Load MARTS data, non-seasonally-adjusted
project, uses("${root}/data/derived/MARTS/MARTS monthly sales by industry.dta")
use if is_adj==0 using "${root}/data/derived/MARTS/MARTS monthly sales by industry.dta", clear
drop is_adj

* Reshape wide on selected industries
gisid month cat_code
keep if inlist(cat_code,"44W72","722")

drop cat_desc
rename sales sales_
greshape wide sales_, i(month) j(cat_code) string

label var sales_44W72 "Monthly sales, in $MLN. 44W72: Retail Trade and Food Services, excl Auto and Gas"
label var sales_722 "Monthly sales, in $MLN. 722: Food Services and Drinking Places"

* Generate retail sales excluding food, auto and gas
gen sales_retail_excl = sales_44W72 - sales_722
label var sales_retail_excl "Monthly sales, in $MLN. Retail Trade, excl Food Services, Auto and Gas"


* Compute daily sales
gen byte days_in_month = dofm(month + 1) - dofm(month)
foreach var of varlist sales_* {
	local ind = subinstr("`var'", "sales_", "", 1)
	
	gen double dailysales_`ind' = `var' / days_in_month
}
drop days_in_month sales_*

* Single norm to January 2019
keep if year(dofm(month))>=2019
sort month
gisid month
assert month[1] == ym(2019, 1)
foreach var of varlist dailysales_* {
	local ind = subinstr("`var'","dailysales_","",1)
	
	gen double singlenorm_`ind' = (`var' / `var'[1]) - 1
}

* Output
rename dailysales_722 marts_spend_food
rename dailysales_retail_excl marts_spend_retail

rename singlenorm_722 marts_snorm_food
rename singlenorm_retail_excl marts_snorm_retail
keep month marts_*

gisid month
tempfile marts
save `marts'

*-------------------------------------------------------------------------------
* Load Affinity data
*-------------------------------------------------------------------------------

* Load National Affinity data published on the Economic Tracker
project, uses("${root}/data/web/data/Affinity - National - Daily.csv")
import delimited "${root}/data/web/data/Affinity - National - Daily.csv", asdouble clear

* Keep relevant variables
keep year month day spend_19_retail_w_grocery spend_19_acf

* Compute average within each month
gisid year month day
gcollapse (mean) spend*, by(year month) fast

rename spend_19_acf affinity_snorm_food
rename spend_19_retail_w_grocery affinity_snorm_retail

* Create Stata month variable
gen ym = ym(year, month)
format %tm ym
drop year month
order ym
rename ym month

* Drop last month of data: likely incomplete
gisid month
sort month
drop in -1

*-------------------------------------------------------------------------------
* Combine Affinity & MARTS data
*-------------------------------------------------------------------------------

* Merge in MARTS
merge 1:1 month using `marts', nogen

* Restrict to desired end date
keep if dofm(month)<=${finaldate}

* Compute Affinity $ values
gisid month
sort month
drop if month == ym(2018,12)
assert month[1] == ym(2019, 1)

foreach cat in food retail {
	gen affinity_spend_`cat' = (affinity_snorm_`cat' + 1) * marts_spend_`cat'[1]
}

* Calculate cross-correlations
corr affinity_snorm_food marts_snorm_retail 
local corr_afffood_mrtsretail : di %4.2f `r(rho)'

corr affinity_snorm_retail marts_snorm_food
local corr_affretail_mrtsfood : di %4.2f `r(rho)'

corr affinity_snorm_retail affinity_snorm_food
local corr_affretail_afffood : di %4.2f `r(rho)'

corr marts_snorm_retail marts_snorm_food
local corr_mrtsretail_mrtsfood : di %4.2f `r(rho)'

* Compute month-on-month changes
sort month
foreach cat in food retail {
	foreach data in affinity marts {
		gen double `data'_momchange_`cat' = 100 * (`data'_spend_`cat' / `data'_spend_`cat'[_n-1] - 1) 
	}
	
	gen sqerror_momchange_`cat' = ( affinity_momchange_`cat' - marts_momchange_`cat' ) ^ 2
}

* Compute RMSE
sum sqerror_momchange_food
local rmse_food: di %4.2f sqrt(`r(mean)')
di `rmse_food'
local ypos1 = 1.5 

sum sqerror_momchange_retail
local rmse_retail: di %4.2f sqrt(`r(mean)')
di `rmse_retail'
local ypos2 = 1.45

* Compute correlations
corr affinity_momchange_food marts_momchange_food
local corr_food : di %4.2f `r(rho)'
di `corr_food'

corr affinity_momchange_retail marts_momchange_retail
local corr_retail : di %4.2f `r(rho)'
di `corr_retail'

* Reshape long on sector
greshape long affinity_snorm affinity_spend affinity_momchange marts_snorm marts_spend marts_momchange sqerror_momchange, i(month) j(sector) string
replace sector = "Food and Accommodation Services" if sector=="_food"
replace sector = "Retail" if sector=="_retail"

*-------------------------------------------------------------------------------
* Plot Affinity vs MARTS month-on-month changes
*-------------------------------------------------------------------------------

rename month year_month
tw  ///
	(connect affinity_momchange year_month, sort col(oi2) msize("vsmall")) /// 
	(connect marts_momchange  year_month, sort color(oi3) msize("vsmall")) ///
	, 	by(sector, note("") imargin(*1.2))  ///
	xtitle("") ///
	ytitle("Month-on-Month Change in" "Consumer Spending (%)") ///
	${fig_title} ///
	yline(0, lcolor(gs8) lpattern(dash)) ///
	yscale(range(-50 50)) ///
	ylabel(-50 "-50%" -25 "-25%" 0 "0%" 25 "+25%" 50 "+50%", nogrid) ///
	xlab(`=mofd(mdy(1, 1, 2019))' `" "Jan" "2019"""'  `=mofd(mdy(7, 1, 2019))' "Jul" ///
		`=mofd(mdy(1, 1, 2020))' `" "Jan" "2020"""'  `=mofd(mdy(7, 1, 2020))' "Jul" ///
        `=mofd(mdy(1, 1, 2021))' `" "Jan" "2021"""' `=mofd(mdy(7, 1, 2021))'  "Jul" ///
		`=mofd(mdy(1, 1, 2022))' `" "Jan" "2022"""') /// 
	legend(pos(7) ring(0) size(2.4) col(2) symxsize(huge) region(lwidth(none) ///
	fcolor(none)) bmargin(0) ///
		order(1  "Affinity Solutions Series" ///
			  2 "Advance Monthly Retail Trade Survey (MARTS)" )) ///
	note("") ///
	subtitle(, fcolor(gs15) lcolor(gs15)) ///
	xtitle("") /// 
	xsize(13) ///
	graphr(margin(r+5))
	
addplot 1: , text(-45 `=mofd(mdy(1, 1, 2022))'  "RMSE: `=`rmse_food'' p.p." "Correlation: `corr_food'", color(gs8) placement(west) justification(left) size(medsmall)) yscale(range(-50 50)) ylabel(-50 "-50%" -25 "-25%" 0 "0%" 25 "+25%" 50 "+50%", nogrid) norescaling
addplot 2: , text(-45 `=mofd(mdy(1, 1, 2022))'  "RMSE: `=`rmse_retail'' p.p." "Correlation: `corr_retail'", color(gs8) placement(west) justification(left) size(medsmall)) yscale(range(-50 50))  ylabel(-50 "-50%" -25 "-25%" 0 "0%" 25 "+25%" 50 "+50%", nogrid)   norescaling
addplot : , legend(pos(7) ring(0) size(*1.4) col(2) symxsize(*1.4) region(lwidth(none) fcolor(none)) bmargin(0) order(1  "Affinity Solutions Series" 2 "Advance Monthly Retail Trade Survey (MARTS)" ))
 
graph export "${root}/results/Spending/Benchmark Affinity Against MRTS - Retail & Food.svg", replace
project, creates("${root}/results/Spending/Benchmark Affinity Against MRTS - Retail & Food.svg")

*-------------------------------------------------------------------------------
* Output numbers for paper
*-------------------------------------------------------------------------------

cap erase "${root}/results/paper numbers/`category'/Retail and Food Services in Affinity Solutions Data vs MARTS.yaml"

yamlout using "${root}/results/paper numbers/`category'/Retail and Food Services in Affinity Solutions Data vs MARTS.yaml", ///
	key("aff_mrts_food_corr") ///
	comment("Correlation of Food and Accommodation Services Affinity vs Food and Accommodation Services MARTS") ///
	value(`corr_food') fmt(%9.2f)

yamlout using "${root}/results/paper numbers/`category'/Retail and Food Services in Affinity Solutions Data vs MARTS.yaml", ///
	key("aff_mrts_food_rmse") ///
	comment("RMSE of Food and Accommodation Services Affinity vs Food and Accommodation Services MARTS (p.p.)") ///
	value(`rmse_food') fmt(%9.2f)
	
yamlout using "${root}/results/paper numbers/`category'/Retail and Food Services in Affinity Solutions Data vs MARTS.yaml", ///
	key("aff_mrts_food_rmse_int") ///
	comment("RMSE of Food and Accommodation Services Affinity vs Food and Accommodation Services MARTS (p.p.), integer") ///
	value(`rmse_food') fmt(%9.0f)

yamlout using "${root}/results/paper numbers/`category'/Retail and Food Services in Affinity Solutions Data vs MARTS.yaml", ///
	key("aff_mrts_ret_corr") ///
	comment("Correlation of Retail Affinity vs Retail MARTS") ///
	value(`corr_retail') fmt(%9.2f)

yamlout using "${root}/results/paper numbers/`category'/Retail and Food Services in Affinity Solutions Data vs MARTS.yaml", ///
	key("aff_mrts_ret_rmse") ///
	comment("RMSE of Retail Affinity vs Retail MARTS (p.p.)") ///
	value(`rmse_retail') fmt(%9.2f)

yamlout using "${root}/results/paper numbers/`category'/Retail and Food Services in Affinity Solutions Data vs MARTS.yaml", ///
	key("aff_mrts_ret_rmse_int") ///
	comment("RMSE of Retail Affinity vs Retail MARTS (p.p.), integer") ///
	value(`rmse_retail') fmt(%9.0f)
	
yamlout using "${root}/results/paper numbers/`category'/Retail and Food Services in Affinity Solutions Data vs MARTS.yaml", ///
	key("aff_ret_food_corr") ///
	comment("Correlation of Retail Affinity vs Food and Accommodation Services Affinity") ///
	value(`corr_affretail_afffood') fmt(%9.2f)

yamlout using "${root}/results/paper numbers/`category'/Retail and Food Services in Affinity Solutions Data vs MARTS.yaml", ///
	key("aff_rent_mrts_food_corr") ///
	comment("Correlation of Retail Affinity vs Food and Accommodation Services MRTS") ///
	value(`corr_affretail_mrtsfood') fmt(%9.2f)

yamlout using "${root}/results/paper numbers/`category'/Retail and Food Services in Affinity Solutions Data vs MARTS.yaml", ///
	key("aff_food_mrts_ret_corr") ///
	comment("Correlation of Food and Accommodation Services Affinity vs Retail MRTS") ///
	value(`corr_afffood_mrtsretail') fmt(%9.2f)
	
yamlout using "${root}/results/paper numbers/`category'/Retail and Food Services in Affinity Solutions Data vs MARTS.yaml",  ///
	key("mrts_food_ret_corr") ///
	comment("Correlation of Food and Accommodation Services MRTS vs Retail MRTS") ///
	value(`corr_mrtsretail_mrtsfood') fmt(%9.2f)

project, creates("${root}/results/paper numbers/`category'/Retail and Food Services in Affinity Solutions Data vs MARTS.yaml")
